/**
 * Smart City use case October 2012 
 */
package com.orange.smartcity.android.db;


import java.util.ArrayList;
import java.util.List;

import com.orange.smartcity.android.SmartCityApplication;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
import static com.orange.smartcity.android.SmartCityCommon.*;

/**
 * @author FAIROUZ OUBENALI
 *
 */
public class SmartCityDataBase {

	public static final String SQLITE_DB_NAME = SmartCityApplication.class
			.getSimpleName() + ".db";
	public static int SQLITE_DB_VERSION = 1;

	public static String SQLITE_EVENT_TABLE_NAME = "event";
	public static String SQLITE_EVENT_ID_COLUMN_NAME = "event_id";
	public static String SQLITE_DEVICE_ID_COLUMN_NAME = "device_id";
	public static String SQLITE_EVENT_TYPE_COLUMN_NAME = "event_type";
	public static String SQLITE_EVENT_TIMESTAMP_COLUMN_NAME = "event_timestamp";
	public static String SQLITE_EVENT_PARAMS_COLUMN_NAME = "event_params";

	static String SQLITE_CREATE_EVENT_TABLE_SQL_REQUEST = "CREATE TABLE "
			+ SQLITE_EVENT_TABLE_NAME + " (" + SQLITE_EVENT_ID_COLUMN_NAME
			+ " INTEGER PRIMARY KEY AUTOINCREMENT, "
			+ SQLITE_DEVICE_ID_COLUMN_NAME + " VARCHAR(250) NOT NULL, "
			+ SQLITE_EVENT_TYPE_COLUMN_NAME + " VARCHAR(250) NOT NULL, "
			+ SQLITE_EVENT_TIMESTAMP_COLUMN_NAME + " INTEGER NOT NULL, "
			+ SQLITE_EVENT_PARAMS_COLUMN_NAME + " VARCHAR(250)" + ")";

	private static class DatabaseHelper extends SQLiteOpenHelper {

		public DatabaseHelper(final Context context) {
			super(context, SQLITE_DB_NAME, null, SQLITE_DB_VERSION);
		}

		@Override
		public void onCreate(final SQLiteDatabase db) {
			db.execSQL(SQLITE_CREATE_EVENT_TABLE_SQL_REQUEST);
		}

		@Override
		public void onUpgrade(final SQLiteDatabase db, final int oldVersion,
				final int newVersion) {
			Log.w(SMART_CITY_DATA_BASE_TAG, "Upgrading database from version "
					+ oldVersion + " to " + newVersion
					+ ", which will destroy all old data");
			db.execSQL("DROP TABLE IF EXISTS " + SQLITE_EVENT_TABLE_NAME);
			onCreate(db);
		}
	}

	private SQLiteDatabase db;
	private final DatabaseHelper dbHelper;

	public SmartCityDataBase(final Context context) {
		dbHelper = new DatabaseHelper(context);
	}

	public void close() {
		dbHelper.close();
	}

	public void deleteEvent(final Event event) {
		db.delete(SQLITE_EVENT_TABLE_NAME, SQLITE_EVENT_ID_COLUMN_NAME + "="
				+ event.eventId, null);
	}

	public List<Event> getEvents() {
		final Cursor cursor = db.query(SQLITE_EVENT_TABLE_NAME, null, null,
				null, null, null, null);
		if (cursor == null)
			return null;
		try {
			final ArrayList<Event> events = new ArrayList<Event>();
			Event event;
			while (true) {
				cursor.moveToNext();
				if (cursor.isAfterLast())
					break;
				event = new Event();
				event.eventId = cursor.getLong(cursor
						.getColumnIndexOrThrow(SQLITE_EVENT_ID_COLUMN_NAME));
				event.deviceId = cursor.getString(cursor
						.getColumnIndexOrThrow(SQLITE_DEVICE_ID_COLUMN_NAME));
				event.eventType = cursor.getString(cursor
						.getColumnIndexOrThrow(SQLITE_EVENT_TYPE_COLUMN_NAME));
				event.timestamp = cursor
						.getLong(cursor
								.getColumnIndexOrThrow(SQLITE_EVENT_TIMESTAMP_COLUMN_NAME));
				event.params = cursor
						.getString(cursor
								.getColumnIndexOrThrow(SQLITE_EVENT_PARAMS_COLUMN_NAME));
				events.add(event);
			}
			return events;
		} finally {
			cursor.close();
		}
	}

	public long insertEvent(final Event event) {
		final ContentValues initialValues = new ContentValues();
		initialValues.put(SQLITE_DEVICE_ID_COLUMN_NAME, event.deviceId);
		initialValues.put(SQLITE_EVENT_TYPE_COLUMN_NAME, event.eventType);
		initialValues.put(SQLITE_EVENT_TIMESTAMP_COLUMN_NAME, event.timestamp);
		initialValues.put(SQLITE_EVENT_PARAMS_COLUMN_NAME, event.params);
		return db.insert(SQLITE_EVENT_TABLE_NAME, null, initialValues);
	}

	public SmartCityDataBase open() throws SQLException {
		db = dbHelper.getWritableDatabase();
		return this;
	}

}
